﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Data;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Caching;
using Microsoft.Practices.EnterpriseLibrary.Logging;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace centraldba_dbrestore_utility.Data
{
    public class SQLRecoveryManager_DB
    {
        #region " Fields "

        private string connStr = Properties.Settings.Default.cnServerList;
        private string serverNameReplace;
        public event SqlInfoMessageEventHandler InfoMessage;
        public event EventHandler ProcessComplete;

        #endregion

        #region " Constructor "

        public SQLRecoveryManager_DB()
        {
            serverNameReplace = new SqlConnection(connStr).DataSource;
        }

        #endregion

        #region " Events "

        protected virtual void OnInfoMessage(SqlInfoMessageEventArgs e)
        {
            if (InfoMessage != null)
            {
                InfoMessage(this, e);
            }
        }
        protected virtual void OnProcessComplete()
        {
            if (ProcessComplete != null)
            {
                ProcessComplete(this, null);
            }
        }

        #endregion

        #region " Database Objects "

        public void SetSqlServerVisibility(string serverName, bool isVisible)
        {
            try
            {
                var db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
                db.ExecuteNonQuery("spDBA_show_managed_server", new object[] { serverName, isVisible });
            }
            catch (Exception ex) { throw ex; }
        }

        public void ExecuteQuery(string serverName, string cmdQuery)
        {
            // Trim Whitespace
            serverName = serverName.Trim();
            cmdQuery = cmdQuery.Trim();

            // Enforce parameter values as mandatory
            if (serverName.Length.Equals(0) || cmdQuery.Length.Equals(0))
            {
                throw new Exception("Missing parameter (serverName and cmdQuery are both mandatory)");
            }
            else
            {
                try
                {
                    SqlDatabase db = new SqlDatabase(connStr.Replace(serverNameReplace, serverName) + ";Asynchronous Processing=True;");
                    SqlConnection cn = (System.Data.SqlClient.SqlConnection)db.CreateConnection();
                    cn.FireInfoMessageEventOnUserErrors = true;
                    cn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
                    {
                        OnInfoMessage(e);
                    };
                    cn.Open();

                    SqlCommand cm = cn.CreateCommand();
                    cm.CommandType = CommandType.Text;
                    cm.CommandTimeout = 0;
                    cm.CommandText = cmdQuery;
                    cm.Prepare();
                    cm.BeginExecuteReader(new AsyncCallback(ExecuteQuery_QueryCallback), cm);
                }
                catch (Exception ex) { throw ex; }
            }
        }
        private void ExecuteQuery_QueryCallback(IAsyncResult result)
        {
            try
            {
                SqlCommand cm = (SqlCommand)result.AsyncState;
                cm.EndExecuteReader(result).Close();
                OnProcessComplete();
            }
            catch (Exception ex) { throw ex; }
        }

        public ObservableCollection<MyServer> GetSqlServers()
        {
            ObservableCollection<MyServer> results = null;

            try
            {
                var db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
                results = new ObservableCollection<MyServer>(db.ExecuteSprocAccessor<MyServer>("spDBA_get_managed_server_list", new object[] { }));
            }
            catch (Exception ex) { throw ex; }

            return results;
        }
        public ObservableCollection<MyServer> GetPossibleSqlServers()
        {
            ObservableCollection<MyServer> results = null;

            try
            {
                var db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
                results = new ObservableCollection<MyServer>(db.ExecuteSprocAccessor<MyServer>("spDBA_get_server_list", new object[] { }));
                results = new ObservableCollection<MyServer>(results.OrderBy(SqlServer => SqlServer.env_server_name));
            }
            catch (Exception ex) { throw ex; }

            return results;
        }
        public ObservableCollection<IGrouping<string, MyServer>> GetSqlServersByCategory()
        {
            ObservableCollection<IGrouping<string, MyServer>> results = null;

            try
            {
                results = new ObservableCollection<IGrouping<string, MyServer>>(GetSqlServers().OrderBy(SqlServer => SqlServer.srv_server_name).GroupBy(SqlServer => SqlServer.srv_type_ro));
            }
            catch (Exception ex) { throw ex; }

            return results;
        }

        public ObservableCollection<MyDatabase> GetDatabases(string serverName)
        {
            ObservableCollection<MyDatabase> results = null;

            if (!serverName.Length.Equals(0))
            {
                try
                {
                    SqlDatabase db = new SqlDatabase(connStr.Replace(serverNameReplace, serverName));
                    results = new ObservableCollection<MyDatabase>(db.ExecuteSprocAccessor<MyDatabase>("spRestoreDatabase_ListDatabases", new object[] { }));
                }
                catch
                {
                    // Try just listing databases (in case no centraldba_db or no sproc)
                    try
                    {
                        SqlDatabase db = new SqlDatabase(connStr.Replace(serverNameReplace, serverName).Replace(Properties.Resources.centraldba_db_name, "master"));
                        results = new ObservableCollection<MyDatabase>(db.ExecuteSqlStringAccessor<MyDatabase>("SELECT name database_name, NULL latest_backup, '' labels FROM master.sys.databases ORDER BY database_name"));
                    }
                    catch (Exception ex1)
                    {
                        throw ex1;
                    }
                }
            }

            return results;
        }
        public ObservableCollection<string> GetLabels(MyDatabase database)
        {
            return new ObservableCollection<string>(database.labels.Split("|".ToCharArray()));
        }

        public ObservableCollection<MyBackup> GetAllBackups(string serverName, string databaseName, string stopat, string backupLabel, bool fileExists, bool recoveryWindow)
        {
            ObservableCollection<MyBackup> results = null;

            if (!serverName.Length.Equals(0))
            {
                try
                {
                    // Declare Variables
                    object start_date;
                    object label;

                    // Parse/validate parameters
                    if (stopat.Trim().Length.Equals(0))
                        start_date = null;
                    else
                        start_date = stopat.Trim();

                    if (backupLabel.Trim().Length.Equals(0))
                        label = null;
                    else
                        label = backupLabel.Trim();

                    // Get resultset
                    SqlDatabase db = new SqlDatabase(connStr.Replace(serverNameReplace, serverName));
                    results = new ObservableCollection<MyBackup>(db.ExecuteSprocAccessor<MyBackup>("spRestoreGetAllBackups", new object[] { databaseName, start_date, label, fileExists, recoveryWindow }));
                }
                catch (Exception ex) { throw ex; }
            }

            // Return a CollectionViewSource with default sort
            return results;
        }

        public ObservableCollection<MyBackup> GetBackups(string serverName, string databaseName, DateTime stopat, string backupLabel)
        {
            ObservableCollection<MyBackup> results = null;

            if (!serverName.Length.Equals(0))
            {
                try
                {
                    SqlDatabase db = new SqlDatabase(connStr.Replace(serverNameReplace, serverName));
                    results = new ObservableCollection<MyBackup>(db.ExecuteSprocAccessor<MyBackup>("spRestoreGetAllBackups", new object[] { databaseName, stopat, backupLabel }));
                }
                catch (Exception ex) { throw ex; }
            }

            return results;
        }
        public ObservableCollection<MyBackup> GetBackups(string serverName, string databaseName, string backupLabel, DateTime stopat)
        {
            ObservableCollection<MyBackup> results = null;

            if (!serverName.Length.Equals(0))
            {
                try
                {
                    SqlDatabase db = new SqlDatabase(connStr.Replace(serverNameReplace, serverName));
                    results = new ObservableCollection<MyBackup>(db.ExecuteSprocAccessor<MyBackup>("spRestoreDatabase_PointInTime", new object[] { databaseName, backupLabel, stopat, false, false, "" }));
                }
                catch (Exception ex) { throw ex; }
            }

            return results;
        }
        public ObservableCollection<MyBackup> GetBackups(string serverName, string databaseName, Int32 backup_set_id, DateTime stopat)
        {
            ObservableCollection<MyBackup> results = null;

            if (!serverName.Length.Equals(0))
            {
                try
                {
                    SqlDatabase db = new SqlDatabase(connStr.Replace(serverNameReplace, serverName));
                    results = new ObservableCollection<MyBackup>(db.ExecuteSprocAccessor<MyBackup>("spRestoreDatabase_BackupSetID", new object[] { databaseName, backup_set_id, false, false, "", stopat }));
                }
                catch (Exception ex) { throw ex; }
            }

            return results;
        }

        public ObservableCollection<MyRestoreScript> getRestoreScripts(string sourceInstance, string destinationInstance, string sourceDatabase, string destinationDatabase, string restoreLabel, Int32 backup_set_id, string stopat_time)
        {
            // Pre-process parameters
            sourceInstance = sourceInstance.Trim();
            destinationInstance = destinationInstance.Trim();
            sourceDatabase = sourceDatabase.Trim();
            destinationDatabase = destinationDatabase.Trim();
            restoreLabel = restoreLabel.Trim();
            stopat_time = stopat_time.Trim();

            // Ensure destinationInstance has been set
            if (destinationInstance.Length.Equals(0))
                destinationInstance = sourceInstance;

            // Ensure destinationDatabase has been set
            if (destinationDatabase.Length.Equals(0))
                destinationDatabase = sourceDatabase;

            // Declare Local Variables
            int iCount, iTotal;
            DateTime d_stopat_time;
            StringBuilder sb = new StringBuilder();
            ObservableCollection<MyRestoreScript> restoreScripts = new ObservableCollection<MyRestoreScript>();
            bool isDestinationSourceDB = (sourceDatabase.Equals(destinationDatabase) || destinationDatabase.Length.Equals(0));

            // Determine the d_stopat_time value
            if (!DateTime.TryParse(stopat_time, out d_stopat_time))
                d_stopat_time = DateTime.Now;

            // Get the list of backups that need to be restored
            ObservableCollection<MyBackup> bkps = GetBackups(sourceInstance, sourceDatabase, backup_set_id, d_stopat_time);

            // If there are files to be restored, then do a restore process
            if (!bkps.Count.Equals(0))
            {
                // Set the database context
                restoreScripts.Add(new MyRestoreScript("Set database context", "USE " + Properties.Resources.centraldba_db_name));

                // Run a differential backup
                if (isDestinationSourceDB)
                    restoreScripts.Add(new MyRestoreScript("Pre-restore backup", "EXEC dbo.spBackupDifferential @database_name = '" + destinationDatabase + "', @label = 'PRE_Restore'"));
                else
                    restoreScripts.Add(new MyRestoreScript("Pre-restore backup", "EXEC dbo.spBackupDifferential @database_name = '" + destinationDatabase + "', @label = 'PRE_Restore_" + sourceDatabase + "_To_" + destinationDatabase + "'"));

                // Remove any open connections to the database to be restored
                restoreScripts.Add(new MyRestoreScript("Kill open database connections", "EXEC dbo.spRestoreKillProcesses @database_name = '" + destinationDatabase + "'"));

                // Loop through the backup files that need to be restored
                iCount = 0;
                iTotal = bkps.Count;
                foreach (MyBackup bkp in bkps)
                {
                    // Restore a backup file
                    sb.Append("EXEC dbo.spRestoreBackupFile ");

                    // Which database
                    sb.Append("@database_name = '" + destinationDatabase + "', ");

                    // Backup File
                    sb.Append("@file_name = '" + bkp.physical_device_name + "', ");

                    // If the first record, replace the existing database
                    if (bkp.Equals(bkps[0]))
                        sb.Append("@replace_existing = 1, ");

                    // Yes, do the restore
                    sb.Append("@verify_only = 0\n");

                    // File counter
                    iCount++;

                    // Add the restore script to the collection
                    restoreScripts.Add(new MyRestoreScript("Restore backup file " + iCount.ToString() + " of " + iTotal.ToString(), sb.ToString()));

                    // Cleanup
                    sb.Clear();
                }

                // Recover the database
                restoreScripts.Add(new MyRestoreScript("Recover the database", "RESTORE DATABASE " + destinationDatabase + " WITH RECOVERY"));

                // Remap Users
                restoreScripts.Add(new MyRestoreScript("Remap any users that can be automatically mapped", "EXEC dbo.spRestoreChangeUsersLogin '" + destinationDatabase + "'"));

                // Run a differential backup
                if (isDestinationSourceDB)
                    restoreScripts.Add(new MyRestoreScript("Post-restore backup", "EXEC dbo.spBackupDifferential @database_name = '" + destinationDatabase + "', @label = 'POST_Restore'"));
                else
                    restoreScripts.Add(new MyRestoreScript("Post-restore backup", "EXEC dbo.spBackupDifferential @database_name = '" + destinationDatabase + "', @label = 'POST_Restore_" + sourceDatabase + "_To_" + destinationDatabase + "'"));

                // Run a transaction log backup
                restoreScripts.Add(new MyRestoreScript("Transaction log backup", "EXEC dbo.spBackupLog @database_name = '" + destinationDatabase + "'"));

                // Post restore operations unique to each database
                sb.AppendLine("USE [" + destinationDatabase + "];");
                sb.AppendLine("IF  EXISTS (SELECT object_id FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spSysUpdateProdCopy]'))");
                if (isDestinationSourceDB)
                {
                    sb.AppendLine("\tEXEC dbo.spSysUpdateProdCopy @simple_mode = 0, @shrink_log_file = 0, @shrink_data_file = 0;");
                    restoreScripts.Add(new MyRestoreScript("Execute spSysUpdateProdCopy", sb.ToString()));
                }
                else
                {
                    sb.AppendLine("\tEXEC dbo.spSysUpdateProdCopy @simple_mode = 1, @shrink_log_file = 1, @shrink_data_file = 1;");
                    restoreScripts.Add(new MyRestoreScript("Execute spSysUpdateProdCopy (change to Simple + Shrink)", sb.ToString()));
                }

                // Cleanup
                sb.Clear();
            }

            // Cleanup
            sb.Clear();
            bkps.Clear();

            // Return the script array
            return restoreScripts;
        }

        #endregion

    }
}
